﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace MakeIT_Bill_Maker
{
    public partial class Statistics : Form
    {
        private Database.DatabaseConnection conn = new Database.DatabaseConnection();

        public Statistics()
        {
            InitializeComponent();
        }

        // 'Generate Statistics' button
        private void btnStatistics_Click(object sender, EventArgs e)
        {
            //Getting penalty rate
            string sqlPenaltyRate = "SELECT PenaltyRate FROM OtherFixed WHERE Index=1";
            DataSet datasetPenaltyRate = conn.Show(sqlPenaltyRate);
            DataTable dtPenaltyRate = datasetPenaltyRate.Tables[0];
            int penaltyRate = int.Parse(dtPenaltyRate.Rows[0].ItemArray[0].ToString());

            //Get MonthlyArrears, calculate penalty, add to Arrears

            string sqlPenalty = "SELECT AptNo, Arrears, MonthlyArrears FROM Users";
            DataSet datasetPenalty = conn.Show(sqlPenalty);
            DataTable dtPenalty = datasetPenalty.Tables[0];
            
            int arrears = 0, monthlyArrears = 0, totArrears = 0, penalty = 0;
            string aptNo = "", sqlTotArrears = "";
            Boolean c = false;

            for (int i = 0; i < dtPenalty.Rows.Count; i++)
            {
                arrears = int.Parse(dtPenalty.Rows[i]["Arrears"].ToString());
                monthlyArrears = int.Parse(dtPenalty.Rows[i]["MonthlyArrears"].ToString());
                aptNo = dtPenalty.Rows[i]["AptNo"].ToString();
                totArrears = 0;

                if (int.Parse(dtPenalty.Rows[i]["Arrears"].ToString()) > 0)
                {
                    penalty = Convert.ToInt32(Math.Ceiling(monthlyArrears * penaltyRate / 100.0));
                    //totArrears = arrears + penalty;
                    //sqlTotArrears = "UPDATE Users SET Arrears = '" + totArrears + "', Penalty = '" + penalty + "' WHERE AptNo='" + aptNo + "'";
                    sqlTotArrears = "UPDATE Users SET Penalty = '" + penalty + "' WHERE AptNo='" + aptNo + "'";
                    c = conn.add(sqlTotArrears);
                }
            }

            //string sql = "SELECT * FROM Users u LEFT JOIN  UsersMonthly m ON m.BillNo = u.AptNo  WHERE MONTH(CurrReadDate) = '" + cmbMonth.Text + "' AND  YEAR(CurrReadDate) = '" + cmbyear.Text + "'   ORDER BY PrevReadDate DESC";
            
            string sql = "SELECT * FROM Users u LEFT JOIN  UsersMonthly m ON m.BillNo = u.AptNo  WHERE MONTH(CurrReadDate) = '" + cmbMonth.Text + "' AND  YEAR(CurrReadDate) = '" + cmbyear.Text + "'";
            DataSet dataset = conn.Show(sql);
            DataTable dt = dataset.Tables[0];

            string sql1 = "SELECT TotalPayments FROM OtherFixed WHERE Index=1";
            DataSet dataset1 = conn.Show(sql1);
            DataTable dt1 = dataset1.Tables[0];

            if (dt.Rows.Count > 0)
            {
                Report.PdfStatistics r = new Report.PdfStatistics();
                r.printPdf(dt, dt1, (int.Parse(cmbMonth.Text) - 1).ToString(), cmbyear.Text);
            }
            else
            {
                MessageBox.Show("No bill for this month.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }

            //Set MonthlyArrears to this month charge
            string thisMonthCharge = "";
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                thisMonthCharge = dt.Rows[i]["TotalAmount"].ToString();
                sqlTotArrears = "UPDATE Users SET MonthlyArrears = '" + thisMonthCharge + "' WHERE AptNo='" + aptNo + "'";
            }
        }

        // Form load
        private void Statistics_Load(object sender, EventArgs e)
        {
            int year;
            for (year = 2011; year < 2212; year++)
            {
                cmbyear.Items.Add(year);
            }

            cmbyear.Text = DateTime.Now.Year.ToString();
            cmbMonth.Text = DateTime.Now.Month.ToString();
        }
    }
}
